﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Threading;
using Common.Utilities;
using Common.Utilities.Dispatcher;

namespace Workflows.Components.TextMining.Collapse
{
    /// <summary>
    /// 
    /// </summary>
    public class TableFieldValueDistributionWorker:IWorker
    {
        private Thread _WorkerThread;
        private TableFieldValueDistributionInstruction _Instruction;
        private TableFieldValueDistributionOutput _Output;
        private RunContext _Context;
        private Guid _JobTicketID;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }

        #region IWorker Members

        public Thread WorkerThread
        {
            get { return _WorkerThread; }
        }

        public InstructionBase Instruction
        {
            get { return _Instruction; }
        }

        public ResultBase Output
        {
            get { return _Output; }
        }

        public Type ComponentType
        {
            get { return typeof(TableFieldValueDistributionComponent); }
        }

        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }

        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._JobTicketID = ticketGuid;
            this._Instruction = (TableFieldValueDistributionInstruction) request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }

        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._JobTicketID = ticketGuid;
            this._Instruction = (TableFieldValueDistributionInstruction)request;
            this._Context = context;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }

        public void DoWork()
        {
            if(this.WorkerStartedEvent !=null)
            {
                this.WorkerStartedEvent(this._JobTicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = Thread.CurrentThread;
            this.CalculateFieldValueDistributions();
        }

        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._JobTicketID, this, this.GetType(), this._Instruction);
            }
            this._WorkerThread = new Thread(new ThreadStart(this.CalculateFieldValueDistributions));
            this._WorkerThread.Start();
        }

        public void CancelWork()
        {
            if(this._WorkerThread !=null && this._WorkerThread.IsAlive)
            {
                this._WorkerThread.Abort();
                if (this.WorkerCancelledEvent != null)
                {
                    this.WorkerCancelledEvent(this._JobTicketID, this.GetType(), this._Instruction);
                }
            }
        }

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        #endregion

        #region work
        private void CalculateFieldValueDistributions()
        {
            Dictionary<string, int> spreads = new Dictionary<string, int>();
            List<ModePercentile> modePercentiles = new List<ModePercentile>();
            double evennessIdx = 0;

            SqlConnection conn=new SqlConnection(this._Instruction.ConnStr);
            TableColumn col = DbUtil.GetTableColumn(DataProviderType.MSSQL, this._Instruction.ConnStr, string.Empty,
                                                    this._Instruction.TableName, this._Instruction.FieldName);
            try
            {
                conn.Open();
                
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = string.Format("select count (distinct {1}) from {0} where {1} is not null",
                                                this._Instruction.TableName,
                                                this._Instruction.FieldName);
                int distinctCount = int.Parse(cmd.ExecuteScalar().ToString());
                if (distinctCount > 0)
                {
                    List<object> values = new List<object>();
                    cmd.CommandText = string.Format("select {1} from {0} where {1} is not null order by {1} asc",
                                                    this._Instruction.TableName, this._Instruction.FieldName);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        values.Add(reader[0]);
                    }
                    reader.Close();
                    object min = values[0];
                    object max = values[values.Count - 1];

                    cmd.CommandText =
                        string.Format(
                            "select {1}, count({1}) as Count from " +
                            "{0} where {1} is not null " +
                            "group by {1} " +
                            "order by count ({1}) asc",
                            this._Instruction.TableName, this._Instruction.FieldName);
                    reader = cmd.ExecuteReader();
                    int rankScore = 0;
                    ModePercentile modePct = new ModePercentile();
                    modePct.AverageFrequency = 0;
                    modePct.Count = 0;
                    modePct.DistinctValues = new List<string>();
                    modePct.Percentile = 0;
                    modePct.SD = 0;
                    List<int> countsByDistinctValues = new List<int>();
                    while (reader.Read())
                    {
                        rankScore++;
                        if (modePct.Count > distinctCount/10)
                        {
                            // calculate SD
                            double sqreSum = 0;
                            foreach (int countByVal in countsByDistinctValues)
                            {
                                sqreSum +=
                                    Math.Pow(countByVal - modePct.AverageFrequency, 2);
                            }
                            modePct.SD = Math.Sqrt(sqreSum/modePct.Count);
                            modePercentiles.Add(modePct);

                            modePct = new ModePercentile();
                            modePct.AverageFrequency = 0;
                            modePct.Count = 0;
                            modePct.DistinctValues = new List<string>();
                            modePct.Percentile = 0;
                            modePct.SD = 0;
                            countsByDistinctValues = new List<int>();
                        }

                        int percentile = rankScore*100/distinctCount;
                        string value = reader[this._Instruction.FieldName].ToString();
                        int count = (int) reader["Count"];
                        if (!modePct.DistinctValues.Contains(value))
                        {
                            modePct.DistinctValues.Add(value);
                        }
                        countsByDistinctValues.Add(count);
                        modePct.Percentile = (int) Math.Floor((double) percentile/10)*10;
                        modePct.Count++;
                        modePct.AverageFrequency += (double) count/(distinctCount/10);
                    }
                    reader.Close();
                    if (modePct.Count > 0)
                    {
                        // calculate SD
                        double sqreSum = 0;
                        foreach (int countByVal in countsByDistinctValues)
                        {
                            sqreSum +=
                                Math.Pow(countByVal - modePct.AverageFrequency, 2);
                        }
                        modePct.SD = Math.Sqrt(sqreSum/modePct.Count);
                        modePercentiles.Add(modePct);
                    }

                    if (modePercentiles.Count > 0)
                    {
                        double totalSum = 0;
                        foreach (ModePercentile modPct in modePercentiles)
                        {
                            totalSum += modPct.AverageFrequency*modPct.Count;
                        }
                        double avgCount = totalSum/distinctCount;
                        double totalSqreDiff = 0;
                        foreach (ModePercentile modPct in modePercentiles)
                        {
                            totalSqreDiff += Math.Pow(modPct.AverageFrequency - avgCount, 2)/avgCount;
                        }
                        evennessIdx = Math.Sqrt(totalSqreDiff/modePercentiles.Count);
                    }

                    if (FieldDataType.IsNumber(col.DbType))
                    {
                        int interval =
                            (int) (double.Parse(max.ToString()) - double.Parse(min.ToString()))/
                            10;
                        int minValue = (int) Math.Floor(double.Parse(min.ToString()));
                        int maxValue = (int) Math.Ceiling(double.Parse(max.ToString()));

                        if (interval >= 1)
                        {
                            int fromValue = minValue;
                            int toValue = fromValue + interval;
                            while (toValue < maxValue)
                            {
                                int count = 0;
                                foreach (object item in values)
                                {
                                    double value = double.Parse(item.ToString());
                                    if (value >= fromValue && value < toValue)
                                    {
                                        count++;
                                    }
                                    if (value > toValue)
                                    {
                                        break;
                                    }
                                }

                                spreads.Add(string.Format("{0}-{1}", fromValue, toValue), count);
                                fromValue = toValue;
                                toValue += interval;
                            }
                        }
                        else
                        {
                            spreads.Add(string.Format("{0}-{1}", minValue, maxValue), values.Count);
                        }
                    }
                    else if (FieldDataType.IsDateTime(col.DbType))
                    {
                        TimeSpan timeInterval =
                            new TimeSpan(
                                (DateTime.Parse(max.ToString()) - DateTime.Parse(min.ToString())).Ticks/
                                10);
                        DateTime minValue = DateTime.Parse(min.ToString());
                        DateTime maxValue = DateTime.Parse(max.ToString());

                        if (timeInterval.Days >= 1)
                        {
                            DateTime fromValue = minValue;
                            DateTime toValue = fromValue.Add(timeInterval);
                            while (toValue < maxValue)
                            {
                                int count = 0;
                                foreach (object item in values)
                                {
                                    DateTime value = DateTime.Parse(item.ToString());
                                    if (value >= fromValue && value < toValue)
                                    {
                                        count++;
                                    }
                                    if (value > toValue)
                                    {
                                        break;
                                    }
                                }

                                spreads.Add(string.Format("{0}-{1}", fromValue, toValue), count);
                                fromValue = toValue;
                                toValue = toValue.Add(timeInterval);
                            }
                        }
                        else
                        {
                            spreads.Add(string.Format("{0}-{1}", minValue, maxValue), values.Count);
                        }
                    }
                    else if (FieldDataType.IsStringType(col.DbType))
                    {
                        string[] stringRange = new string[]
                                                   {
                                                       "A", "B", "C", "D", "E", "F", "G",
                                                       "H", "I", "J", "K", "L", "M", "N",
                                                       "O", "P", "Q", "R", "S", "T", "U",
                                                       "V", "W", "X", "Y", "Z"
                                                   };
                        foreach (object item in values)
                        {
                            string value = item.ToString();
                            bool found = false;
                            for (int i = 0; i < stringRange.Length; i++)
                            {
                                if (value.ToUpper().StartsWith(stringRange[i]))
                                {
                                    found = true;
                                    Range<IComparable> range = new Range<IComparable>(stringRange[i], stringRange[i]);
                                    if (
                                        spreads.ContainsKey(string.Format("{0}-{1}", range.FromValue, range.ToValue)))
                                    {
                                        int count =
                                            spreads[string.Format("{0}-{1}", range.FromValue, range.ToValue)];
                                        spreads[string.Format("{0}-{1}", range.FromValue, range.ToValue)] = count +
                                                                                                                 1;
                                    }
                                    else
                                    {
                                        spreads.Add(string.Format("{0}-{1}", range.FromValue, range.ToValue), 1);
                                    }
                                    break;
                                }
                            }
                            if (!found)
                            {
                                Range<IComparable> range = new Range<IComparable>("_", "_");
                                if (spreads.ContainsKey(string.Format("{0}-{1}", range.FromValue, range.ToValue)))
                                {
                                    int count = spreads[string.Format("{0}-{1}", range.FromValue, range.ToValue)];
                                    spreads[string.Format("{0}-{1}", range.FromValue, range.ToValue)] = count + 1;
                                }
                                else
                                {
                                    spreads.Add(string.Format("{0}-{1}", range.FromValue, range.ToValue), 1);
                                }
                            }
                        }
                    }
                }

                this._Output = new TableFieldValueDistributionOutput(this._Instruction.OwnerComponentID,
                                                                     this._JobTicketID, this._Instruction.TableName,
                                                                     this._Instruction.FieldName, col.DbType,
                                                                     spreads, modePercentiles, evennessIdx);
                if(this.WorkerFinishedEvent!=null)
                {
                    this.WorkerFinishedEvent(this._JobTicketID, this.GetType(), this._Instruction, this._Output);
                }
            }
            catch(Exception ex)
            {
                if(this.WorkerErredEvent !=null)
                {
                    this.WorkerErredEvent(this._JobTicketID, this.GetType(), this._Instruction, ex.ToString(),true);
                }
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion
    }
}
